import psycopg2

class Postgresql():
    # 初始化数据库连接参数
    def __init__(self, **connection_params):
        self.connection_params = connection_params
        self.connection = None
        self.cursor = None

    # 创建数据库连接和游标对象的函数
    def create_connection(self):
        self.connection = psycopg2.connect(**self.connection_params)
        self.cursor = self.connection.cursor()
        return self.connection, self.cursor

    # 执行查询函数
    def execute_query(self, sql_sentence, params=None):
        result = []
        try:
            self.cursor.execute(sql_sentence, params)
            rows = self.cursor.fetchall()

            for row in rows:
                row_dict = {self.cursor.description[i].name: value for i, value in enumerate(row)}
                result.append(row_dict)

        except (Exception, psycopg2.Error) as error:
            print("Error while connecting to PostgreSQL", error)
        
        return result
    
    # 执行插入操作函数
    def execute_insert(self, table_name, data):
        result = False
        try:
            columns = data.keys()
            values = tuple(data.values())
            columns_str = ', '.join(columns)
            placeholders = ', '.join(['%s' for _ in range(len(values))])
            sql_sentence = f"INSERT INTO {table_name} ({columns_str}) VALUES ({placeholders})"
            
            self.cursor.execute(sql_sentence, values)
            # self.connection.commit()
            result = True

        except (Exception, psycopg2.Error) as error:
            print("Error while connecting to PostgreSQL", error)
        
        return result
    
    def execute_commit(self):
        self.connection.commit()
        

    def quit_connection(self):
        if self.cursor:
            self.cursor.close()
        if self.connection:
            self.connection.close()
        return True
